﻿using ExcelUtil.ExcelAttribute;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Utils;

namespace ExcelUtil
{
    public class ExcelHelper
    {
        #region 导出报表       
        /// <summary>
        /// Workbook 转化为stream
        /// </summary>
        /// <param name="IWorkbook">workbook</param>
        /// <returns></returns>
        public static Stream WorkbookToStream(IWorkbook workbook)
        {
            MemoryStream stream = new MemoryStream();
            //将工作薄写入文件流
            workbook.Write(stream);
            workbook.Close();
            stream.Close();
            stream.Position = 0; 
            return stream;
        }
        /// <summary>
        /// 导出Excel 支持多Sheet
        /// </summary>
        /// <param name="dataResources"></param>
        /// <returns></returns>
        public static IWorkbook DataToHSSFWorkbook(List<ExcelDataResource> dataResources)
        {
            HSSFWorkbook _Workbook = new HSSFWorkbook();

            if (dataResources == null || dataResources.Count == 0)
            {
                return _Workbook;
            }
            foreach (var sheetResource in dataResources)
            {
                if (sheetResource.SheetDataResource == null || sheetResource.SheetDataResource.Count == 0)
                {
                    continue;
                }
                ISheet sheet = _Workbook.CreateSheet(sheetResource.SheetName);
                object obj = sheetResource.SheetDataResource[0];
                Type type = obj.GetType();
                List<PropertyInfo> propList = type.GetProperties().Where(c => c.IsDefined(typeof(ExcelExportAttribute), true)).ToList();

                int titleIndex = 0;
                if (sheetResource.TitleIndex >= 0)
                {
                    titleIndex = sheetResource.TitleIndex - 1;
                }

                IRow titleRow = sheet.CreateRow(titleIndex);

                ICellStyle style = _Workbook.CreateCellStyle();
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
                style.FillPattern = FillPattern.SolidForeground;
   
                titleRow.Height = 100 * 4;
                //表头
                for (int i = 0; i < propList.Count(); i++)
                {
                    ExcelExportAttribute propertyAttribute = propList[i]?.GetCustomAttribute<ExcelExportAttribute>()!;
                    ICell cell = titleRow.CreateCell(i);
                    cell.SetCellValue(propertyAttribute?.FieldName);
                    cell.CellStyle = style;
                    cell.CellStyle.WrapText = true;
                    cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
                    cell.CellStyle.Alignment = HorizontalAlignment.Center;

                }
                //数据
                for (int i = 0; i < sheetResource.SheetDataResource.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1 + titleIndex);
                    object objInstance = sheetResource.SheetDataResource[i];
                    for (int j = 0; j < propList.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);

                        cell.SetCellValue(propList[j].GetValue(objInstance)?.ToString());

                        cell.CellStyle.WrapText = true;
                        cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
                        cell.CellStyle.Alignment = HorizontalAlignment.Left;

                    }
                }
            }

            return _Workbook;
        }
        #endregion

        #region 根据模版导出报表     

        public static Stream GererateReportStreamV2<T>(List<T> list)
        {
            if (list.Count == 0) throw new Exception("数据为空");

            var stream = new MemoryStream();
            var newFileName = $"天气信息_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
            var newFile = RuYiFileContext.GenerateNewFileInfo(@"Template/Report", "天气信息.xls", newFileName);

            var type = typeof(T);
            var properties = type.GetProperties().Where(p => p.GetCustomAttributes(typeof(ExcelExportAttribute), true).Length > 0).ToList();

            //List<PropertyInfo> propList = type.GetProperties().Where(c => c.IsDefined(typeof(ExcelExportAttribute), true)).ToList();

            //起始行
            int startRowNum = 2;

            using (FileStream file = newFile.Open(FileMode.Open))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                // 获取第一个工作表  
                ISheet sheet = workbook.GetSheetAt(0);

                //数据
                for (int i = 0; i < list.Count; i++)
                {
                    IRow row = sheet.GetRow(i + startRowNum) ?? sheet.CreateRow(i + startRowNum); // 如果行不存在则创建  

                    object objInstance = list[i];
                    for (int j = 0; j < properties.Count; j++)
                    {
                        ICell cell = row.GetCell(j) ?? row.CreateCell(j); // 如果单元格不存在则创建  

                        cell.SetCellValue(properties[j].GetValue(objInstance)?.ToString());

                        cell.CellStyle.WrapText = true;
                        cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
                        cell.CellStyle.Alignment = HorizontalAlignment.Left;

                    }
                }
                //将工作薄写入文件流
                workbook.Write(stream);
                workbook.Close();
            }
            stream.Close();
            stream.Position = 0;
            return stream;
        }
        private Stream GererateReportStream<T>(List<T> list)
        {
            if (list.Count == 0) throw new Exception("数据为空");

            var stream = new MemoryStream();
            var newFileName = $"天气信息_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
            var newFile = RuYiFileContext.GenerateNewFileInfo(@"Template/Report", "天气信息.xls", newFileName);

            var type = typeof(T);
            var properties = type.GetProperties().Where(p => p.GetCustomAttributes(typeof(ExcelExportAttribute), true).Length > 0).ToList();

            //起始行 = 5
            int startRowNum = 2, columnNum = 0;

            using (FileStream file = newFile.Open(FileMode.Open))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                // 获取第一个工作表  
                ISheet sheet = workbook.GetSheetAt(0);

                // 假设我们要向第一行第一列写入数据  
                IRow row = sheet.GetRow(startRowNum) ?? sheet.CreateRow(startRowNum); // 如果行不存在则创建  
                ICell cell = row.GetCell(columnNum) ?? row.CreateCell(columnNum); // 如果单元格不存在则创建  

                cell.SetCellValue("Hello, NPOI!"); // 设置单元格的值  

                cell.CellStyle.WrapText = true;
                cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
                cell.CellStyle.Alignment = HorizontalAlignment.Left;
                //将工作薄写入文件流
                workbook.Write(stream);
                workbook.Close();
            }
            stream.Position = 0;
            return stream;
        }

        #endregion

        #region 导入报表
        /// <summary>
        /// Excel 转化为DataTable
        /// 支持多Sheet 一般只用一个
        /// </summary>
        /// <param name="hssfWorkbook"></param>
        /// <returns></returns>
        public static List<DataTable> ToExcelDataTable(IWorkbook hssfWorkbook)
        {
            List<DataTable> dataTables = new List<DataTable>();
            for (int sheetIndex = 0; sheetIndex < hssfWorkbook.NumberOfSheets; sheetIndex++)
            {
                ISheet sheet = hssfWorkbook.GetSheetAt(sheetIndex);
                IRow header=sheet.GetRow(sheet.FirstRowNum);
                if (header == null)
                {
                    break;
                }
                int startRow = 0;
                DataTable dtNpoi = new DataTable();
                startRow = sheet.FirstRowNum + 1;
                //表头
                for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
                {
                    ICell cell = header.GetCell(i);
                    if (cell !=null)
                    {
                        string cellValue = cell.ToString();
                        if (cellValue != null)
                        {
                            DataColumn col = new DataColumn(cellValue);
                            dtNpoi.Columns.Add(col);
                        }
                        else
                        {
                            DataColumn col = new DataColumn();
                            dtNpoi.Columns.Add(col);
                        }
                    }
                }
                //数据
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row=sheet.GetRow(i);//获取行
                    if (row == null)
                    {
                        continue;
                    }
                    DataRow dr=dtNpoi.NewRow();
                    //遍历每行的单元格
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dr[j] = row.GetCell(j).ToString();
                        }
                    }
                    dtNpoi.Rows.Add(dr);
                }
                dataTables.Add(dtNpoi);
            } 
            return dataTables;
        }

        /// <summary>
        /// stream 转化为DataTable
        /// </summary>
        /// <param name="hssfWorkbook"></param>
        /// <returns></returns>
        public static List<DataTable> ExcelStreamToDataTable(Stream stream)
        {
            stream.Position = 0;
            //var workbook = new XSSFWorkbook(stream);
            //var worksheet = workbook.GetSheetAt(0);
            IWorkbook workbook = WorkbookFactory.Create(stream);
            return ToExcelDataTable(workbook);
        }
        #endregion

    }
}
